--select * from Dist_Shipping_Lot_Table
-- EXEC SP_CREATE_UPDATE_DIST_SO_INVENTORY_DETAILS -1,610,1,-1,'chalot',37
ALTER PROC SP_CREATE_UPDATE_DIST_SO_INVENTORY_DETAILS
(
	@DISTRIBUTOR_SALES_ORDER_ID INT,
	@DISTRIBUTOR_ID		INT,
	@DIST_LINEITEM_ID	INT,
	@PRODUCT_ID			INT,
	@LOT_ID			INT,
	@LOT_NUMBER		VARCHAR(50),
	@PULLED_QTY		INT,
	@CSLIT_ID INT 
)
AS
BEGIN 	
	DECLARE @DIST_INVENTORY_CURSOR CURSOR
	DECLARE @D_CUSTOMER_ID 	INT
	DECLARE @D_CSLIT_ID	INT
	DECLARE @D_LOT_NUMBER	VARCHAR(50)
	DECLARE @D_QTY_IN 	INT
	DECLARE @D_SHIPPED_QTY	INT
	DECLARE @PREVIOUS_PULLED_QTY INT 	
	DECLARE @ACTUALD_PULLED_QTY 	INT
	DECLARE @PARTIAL_PULLED_QTY 	INT
	DECLARE @QTY 			INT

	SELECT @LOT_NUMBER = Lot_Number FROM Dist_Shipping_Lot_Table WHERE DIST_LINEITEM_ID = @DIST_LINEITEM_ID AND Lot_Id =@LOT_ID AND DISTRIBUTOR_SALES_ORDER_ID =@DISTRIBUTOR_SALES_ORDER_ID
	SELECT @PREVIOUS_PULLED_QTY= ISNULL(PULLED_QTY,0) FROM Dist_Shipping_Lot_Table WHERE DIST_LINEITEM_ID = @DIST_LINEITEM_ID AND Lot_Id = @LOT_ID AND DISTRIBUTOR_SALES_ORDER_ID =@DISTRIBUTOR_SALES_ORDER_ID
	IF( @@ROWCOUNT = 0 )
	BEGIN
		IF( @PULLED_QTY > 0 )
		BEGIN
			INSERT INTO Dist_Shipping_Lot_Table( DISTRIBUTOR_SALES_ORDER_ID ,Dist_LineItem_Id, Lot_Id, Lot_Number, Pulled_Qty ) 
				VALUES( @DISTRIBUTOR_SALES_ORDER_ID, @DIST_LINEITEM_ID, @LOT_ID, @LOT_NUMBER, @PULLED_QTY )
		END
	END
	ELSE
	BEGIN
		UPDATE Dist_Shipping_Lot_Table SET Pulled_Qty = @PULLED_QTY + @PREVIOUS_PULLED_QTY WHERE DIST_LINEITEM_ID = @DIST_LINEITEM_ID AND LOT_ID = @LOT_ID AND DISTRIBUTOR_SALES_ORDER_ID=@DISTRIBUTOR_SALES_ORDER_ID
	END

	UPDATE DISTRIBUTOR_INVENTORY_LOT SET SHIPPED_QTY = ISNULL( SHIPPED_QTY, 0 ) + @PULLED_QTY WHERE CSLIT_ID = @CSLIT_ID AND LOT_ID = @LOT_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID
		AND PROCESSSTEP_ID = (SELECT PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_ID )
END
